# 附件1-数据存在4张表，分别为Area1_Load,Area1_Weather,Area2_Load,Area2_Weather
# 将这4张表数据分别转换为4个csv文件，文件名分别为Area1_Load.csv,Area1_Weather.csv,Area2_Load.csv,Area2_Weather.csv
#step1 转文档格式
import pandas as pd

data = pd.read_excel('data/1-suc.xlsx', sheet_name = None) # sheet_name = None means read all sheets
data.get('Area1-Load') # get a specific sheet to DataFrame

# 保存为csv文件
data.get('Load').to_csv('data/Load.csv', index = False)
data.get('Weather').to_csv('data/Weather.csv', index = False)
#data.get('Area2_Load').to_csv('Area2_Load.csv', index = False)
#data.get('Area2_Weather').to_csv('Area2_Weather.csv', index = False)


# Area1_Weather.csv第一列为日期，但名称为Unnamed: 0，将其改为YMD
data = pd.read_csv('data/Weather.csv')
#data.rename(columns = {'Unnamed: 0':'YMD'}, inplace = True)
# 将YMD列转换为日期格式
data['YMD'] = pd.to_datetime(data['YMD'], format='%Y%m%d')
data.to_csv('data/Weather.csv', index = False)
# Area2_Weather.csv同理
#data = pd.read_csv('Area2_Weather.csv')
#data.rename(columns = {'Unnamed: 0':'YMD'}, inplace = True)
# 将YMD列转换为日期格式
#data['YMD'] = pd.to_datetime(data['YMD'], format='%Y%m%d')
#data.to_csv('Area2_Weather.csv', index = False)

# 对于Area1_Load.csv进行日期格式转换
data = pd.read_csv('data/Load.csv')
data['YMD'] = pd.to_datetime(data['YMD'], format='%Y%m%d')
data.to_csv('data/Load.csv', index = False)
# Area2_Load.csv同理
#data = pd.read_csv('Area2_Load.csv')
#data['YMD'] = pd.to_datetime(data['YMD'], format='%Y%m%d')
#data.to_csv('Area2_Load.csv', index = False)

import pandas as pd
# Area1_Weather.csv的列名读取错误
# YMD,最高温度℃,最低温度℃平均温度℃相对湿度(平均),Unnamed: 3,Unnamed: 4,降雨量（mm）
data = pd.read_csv('data/Weather.csv')
data.columns = ['YMD', 'Max_Temperature', 'Min_Temperature', 'Avg_Temperature', 'Avg_Humidity', 'Rainfall']
data.to_csv('data/Weather.csv', index = False)
# Area2_Weather.csv同理
#data = pd.read_csv('Area2_Weather.csv')
#data.columns = ['YMD', 'Max_Temperature', 'Min_Temperature', 'Avg_Temperature', 'Avg_Humidity', 'Rainfall']
#data.to_csv('Area2_Weather.csv', index = False)